{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The MIT License (MIT)\n",
    "\n",
    "# Copyright (c) 2020, NVIDIA CORPORATION.\n",
    "\n",
    "# Permission is hereby granted, free of charge, to any person obtaining a copy of\n",
    "# this software and associated documentation files (the \"Software\"), to deal in\n",
    "# the Software without restriction, including without limitation the rights to\n",
    "# use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of\n",
    "# the Software, and to permit persons to whom the Software is furnished to do so,\n",
    "# subject to the following conditions:\n",
    "\n",
    "# The above copyright notice and this permission notice shall be included in all\n",
    "# copies or substantial portions of the Software.\n",
    "\n",
    "# THE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\n",
    "# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS\n",
    "# FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR\n",
    "# COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER\n",
    "# IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN\n",
    "# CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tutorial: Feature Engineering for Recommender Systems\n",
    "\n",
    "# 5. Feature Engineering - TimeSeries\n",
    "\n",
    "## 5.1. Historical Events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import IPython\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import cudf\n",
    "import cupy\n",
    "\n",
    "np.random.seed(42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "itemid = [1000001]*10 + [1000002]*5 + [1000001]*5 + [1000002]*5 + [1000001]*1 + [1000002]*1 + [1000001]*2 + [1000002]*2\n",
    "itemid += [1000001]*3 + [1000002]*2 + [1000001]*1 + [1000002]*1 + [1000001]*6 + [1000002]*3 + [1000001]*2 + [1000002]*2\n",
    "userid = np.random.choice(list(range(10000)), len(itemid))\n",
    "action = np.random.choice(list(range(2)), len(itemid), p=[0.2, 0.8])\n",
    "timestamp = [pd.to_datetime('2020-01-01')]*15\n",
    "timestamp += [pd.to_datetime('2020-01-02')]*10\n",
    "timestamp += [pd.to_datetime('2020-01-03')]*2\n",
    "timestamp += [pd.to_datetime('2020-01-04')]*4\n",
    "timestamp += [pd.to_datetime('2020-01-05')]*5\n",
    "timestamp += [pd.to_datetime('2020-01-07')]*2\n",
    "timestamp += [pd.to_datetime('2020-01-08')]*9\n",
    "timestamp += [pd.to_datetime('2020-01-09')]*4\n",
    "\n",
    "data = pd.DataFrame({\n",
    "    'itemid': itemid,\n",
    "    'userid': userid,\n",
    "    'action': action,\n",
    "    'timestamp': timestamp\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = cudf.from_pandas(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Theory"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Many real-world recommendation systems contain time information. The system normally logs events with a timestamp. Tree-based or deep learning based models usually only uses the information from the datapoint itself for the prediction and they have difficulties to capture relationships over multiple datapoints."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at a simple example. Let's assume we have the interaction events of an itemid, userid and action with the timestamp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>userid</th>\n",
       "      <th>action</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000001</td>\n",
       "      <td>7270</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000001</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5390</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5191</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5734</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1000001</td>\n",
       "      <td>6265</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1000001</td>\n",
       "      <td>466</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1000001</td>\n",
       "      <td>4426</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5578</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8322</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5051</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1000001</td>\n",
       "      <td>6420</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1000001</td>\n",
       "      <td>1184</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1000001</td>\n",
       "      <td>4555</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1000001</td>\n",
       "      <td>3385</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2047</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1000001</td>\n",
       "      <td>9167</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1000001</td>\n",
       "      <td>9998</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1000001</td>\n",
       "      <td>3005</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1000001</td>\n",
       "      <td>4658</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1000001</td>\n",
       "      <td>1899</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1000001</td>\n",
       "      <td>1528</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1000001</td>\n",
       "      <td>3890</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8838</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5393</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8792</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8433</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>1000001</td>\n",
       "      <td>7513</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1000001</td>\n",
       "      <td>6235</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5486</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     itemid  userid  action  timestamp\n",
       "0   1000001    7270       1 2020-01-01\n",
       "1   1000001     860       1 2020-01-01\n",
       "2   1000001    5390       0 2020-01-01\n",
       "3   1000001    5191       1 2020-01-01\n",
       "4   1000001    5734       0 2020-01-01\n",
       "5   1000001    6265       1 2020-01-01\n",
       "6   1000001     466       1 2020-01-01\n",
       "7   1000001    4426       1 2020-01-01\n",
       "8   1000001    5578       1 2020-01-01\n",
       "9   1000001    8322       0 2020-01-01\n",
       "15  1000001    5051       1 2020-01-02\n",
       "16  1000001    6420       1 2020-01-02\n",
       "17  1000001    1184       1 2020-01-02\n",
       "18  1000001    4555       1 2020-01-02\n",
       "19  1000001    3385       1 2020-01-02\n",
       "25  1000001    2047       1 2020-01-03\n",
       "27  1000001    9167       0 2020-01-04\n",
       "28  1000001    9998       0 2020-01-04\n",
       "31  1000001    3005       1 2020-01-05\n",
       "32  1000001    4658       0 2020-01-05\n",
       "33  1000001    1899       0 2020-01-05\n",
       "36  1000001    1528       1 2020-01-07\n",
       "38  1000001    3890       1 2020-01-08\n",
       "39  1000001    8838       1 2020-01-08\n",
       "40  1000001    5393       1 2020-01-08\n",
       "41  1000001    8792       1 2020-01-08\n",
       "42  1000001    8433       0 2020-01-08\n",
       "43  1000001    7513       1 2020-01-08\n",
       "47  1000001    6235       1 2020-01-09\n",
       "48  1000001    5486       1 2020-01-09"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['itemid']==1000001]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can extract many interesting features based on the history, such as\n",
    "* the sum number of actions of the last day, last 3 days or last 7 days\n",
    "* the average number of actions of the last day, last 3 days or last 7 days\n",
    "* the average probability of the last day, last 3 days or last 7 days\n",
    "* etc.\n",
    "\n",
    "In general, these operations are called window function and uses `.rolling()` function. For each row, the function looks at a window (# of rows around it) and apply a certain function to it.\n",
    "\n",
    "Current, our data is on a userid and itemid level. First, we need to aggregate it on the level, we want to apply the window function. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_window = data[['itemid', 'timestamp', 'action']].groupby(['itemid', 'timestamp']).agg(['count', 'sum']).reset_index()\n",
    "data_window.columns = ['itemid', 'timestamp', 'count', 'sum']\n",
    "data_window.index = data_window['timestamp']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-05</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-07</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-08</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-09</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-05</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-07</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-08</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-09</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             itemid  timestamp  count  sum\n",
       "timestamp                                 \n",
       "2020-01-01  1000001 2020-01-01     10    7\n",
       "2020-01-02  1000001 2020-01-02      5    5\n",
       "2020-01-03  1000001 2020-01-03      1    1\n",
       "2020-01-04  1000001 2020-01-04      2    0\n",
       "2020-01-05  1000001 2020-01-05      3    1\n",
       "2020-01-07  1000001 2020-01-07      1    1\n",
       "2020-01-08  1000001 2020-01-08      6    5\n",
       "2020-01-09  1000001 2020-01-09      2    2\n",
       "2020-01-01  1000002 2020-01-01      5    5\n",
       "2020-01-02  1000002 2020-01-02      5    5\n",
       "2020-01-03  1000002 2020-01-03      1    1\n",
       "2020-01-04  1000002 2020-01-04      2    2\n",
       "2020-01-05  1000002 2020-01-05      2    2\n",
       "2020-01-07  1000002 2020-01-07      1    1\n",
       "2020-01-08  1000002 2020-01-08      3    3\n",
       "2020-01-09  1000002 2020-01-09      2    2"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_window"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are interested how many positive interaction an item had on the previous day. Next, we want to groupby our dataframe by itemid. Then we apply the rolling function for two days (`2D`).\n",
    "\n",
    "**Note:** To use the rolling function with days, the dataframe index has to by a timestamp.\n",
    "\n",
    "We can see that every row contains the sum of the row value + the previous row value.\n",
    "For example, `itemid=1000001` for data `2020-01-02` counts 15 observations and sums 12 positive interactions.<br><br>\n",
    "What happend on the date `2020-01-07`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>itemid</th>\n",
       "      <th>timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">1000001</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-05</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-07</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-08</th>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-09</th>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">1000002</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-05</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-07</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-08</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-09</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    count  sum\n",
       "itemid  timestamp             \n",
       "1000001 2020-01-01     10    7\n",
       "        2020-01-02     15   12\n",
       "        2020-01-03     16   13\n",
       "        2020-01-04      8    6\n",
       "        2020-01-05      6    2\n",
       "        2020-01-07      4    2\n",
       "        2020-01-08      7    6\n",
       "        2020-01-09      9    8\n",
       "1000002 2020-01-01      5    5\n",
       "        2020-01-02     10   10\n",
       "        2020-01-03     11   11\n",
       "        2020-01-04      8    8\n",
       "        2020-01-05      5    5\n",
       "        2020-01-07      3    3\n",
       "        2020-01-08      4    4\n",
       "        2020-01-09      6    6"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "offset = '3D'\n",
    "\n",
    "data_window_roll = data_window[['itemid', 'count', 'sum']].groupby(['itemid']).rolling(offset).sum().drop('itemid', axis=1)\n",
    "data_window_roll"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we take a look on the calculations, we see that the `.rolling()` inclues the value from the current row, as well. This could be a kind of data leakage. Therefore, we shift the values by one row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_window_roll = data_window_roll.reset_index()\n",
    "data_window_roll.columns = ['itemid', 'timestamp', 'count_' + offset, 'sum_' + offset]\n",
    "data_window_roll[['count_' + offset, 'sum_' + offset]] = data_window_roll[['count_' + offset, 'sum_' + offset]].shift(1)\n",
    "data_window_roll.loc[data_window_roll['itemid']!=data_window_roll['itemid'].shift(1), ['count_' + offset, 'sum_' + offset]] = 0\n",
    "data_window_roll['avg_' + offset] = data_window_roll['sum_' + offset]/data_window_roll['count_' + offset]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>count_3D</th>\n",
       "      <th>sum_3D</th>\n",
       "      <th>avg_3D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>0.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "      <td>0.812500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>0.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>0.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     itemid  timestamp  count_3D  sum_3D    avg_3D\n",
       "0   1000001 2020-01-01         0       0       NaN\n",
       "1   1000001 2020-01-02        10       7  0.700000\n",
       "2   1000001 2020-01-03        15      12  0.800000\n",
       "3   1000001 2020-01-04        16      13  0.812500\n",
       "4   1000001 2020-01-05         8       6  0.750000\n",
       "5   1000001 2020-01-07         6       2  0.333333\n",
       "6   1000001 2020-01-08         4       2  0.500000\n",
       "7   1000001 2020-01-09         7       6  0.857143\n",
       "8   1000002 2020-01-01         0       0       NaN\n",
       "9   1000002 2020-01-02         5       5  1.000000\n",
       "10  1000002 2020-01-03        10      10  1.000000\n",
       "11  1000002 2020-01-04        11      11  1.000000\n",
       "12  1000002 2020-01-05         8       8  1.000000\n",
       "13  1000002 2020-01-07         5       5  1.000000\n",
       "14  1000002 2020-01-08         3       3  1.000000\n",
       "15  1000002 2020-01-09         4       4  1.000000"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_window_roll"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After we calculated the aggregated values and applied the window function, we want to merge it to our original dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.merge(data_window_roll, how='left', on=['itemid', 'timestamp'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>userid</th>\n",
       "      <th>action</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>count_3D</th>\n",
       "      <th>sum_3D</th>\n",
       "      <th>avg_3D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000001</td>\n",
       "      <td>4658</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>0.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000001</td>\n",
       "      <td>1899</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>0.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000002</td>\n",
       "      <td>7734</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000002</td>\n",
       "      <td>1267</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000001</td>\n",
       "      <td>1528</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1000002</td>\n",
       "      <td>3556</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1000001</td>\n",
       "      <td>3890</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8838</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5393</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8792</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8433</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1000001</td>\n",
       "      <td>7513</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2612</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1000002</td>\n",
       "      <td>7041</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1000002</td>\n",
       "      <td>9555</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1000001</td>\n",
       "      <td>6235</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>0.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5486</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>0.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1000002</td>\n",
       "      <td>7099</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1000002</td>\n",
       "      <td>9670</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1000001</td>\n",
       "      <td>7270</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1000001</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5390</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5191</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5734</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1000001</td>\n",
       "      <td>6265</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1000001</td>\n",
       "      <td>466</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1000001</td>\n",
       "      <td>4426</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5578</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8322</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1000002</td>\n",
       "      <td>1685</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1000002</td>\n",
       "      <td>769</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1000002</td>\n",
       "      <td>6949</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2433</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1000002</td>\n",
       "      <td>5311</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5051</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1000001</td>\n",
       "      <td>6420</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1000001</td>\n",
       "      <td>1184</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>1000001</td>\n",
       "      <td>4555</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1000001</td>\n",
       "      <td>3385</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1000002</td>\n",
       "      <td>6396</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1000002</td>\n",
       "      <td>8666</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>1000002</td>\n",
       "      <td>9274</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2558</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>1000002</td>\n",
       "      <td>7849</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2047</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>0.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2747</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>1000001</td>\n",
       "      <td>9167</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "      <td>0.812500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1000001</td>\n",
       "      <td>9998</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "      <td>0.812500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>1000002</td>\n",
       "      <td>189</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2734</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>1000001</td>\n",
       "      <td>3005</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>0.750000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     itemid  userid  action  timestamp  count_3D  sum_3D    avg_3D\n",
       "0   1000001    4658       0 2020-01-05         8       6  0.750000\n",
       "1   1000001    1899       0 2020-01-05         8       6  0.750000\n",
       "2   1000002    7734       1 2020-01-05         8       8  1.000000\n",
       "3   1000002    1267       1 2020-01-05         8       8  1.000000\n",
       "4   1000001    1528       1 2020-01-07         6       2  0.333333\n",
       "5   1000002    3556       1 2020-01-07         5       5  1.000000\n",
       "6   1000001    3890       1 2020-01-08         4       2  0.500000\n",
       "7   1000001    8838       1 2020-01-08         4       2  0.500000\n",
       "8   1000001    5393       1 2020-01-08         4       2  0.500000\n",
       "9   1000001    8792       1 2020-01-08         4       2  0.500000\n",
       "10  1000001    8433       0 2020-01-08         4       2  0.500000\n",
       "11  1000001    7513       1 2020-01-08         4       2  0.500000\n",
       "12  1000002    2612       1 2020-01-08         3       3  1.000000\n",
       "13  1000002    7041       1 2020-01-08         3       3  1.000000\n",
       "14  1000002    9555       1 2020-01-08         3       3  1.000000\n",
       "15  1000001    6235       1 2020-01-09         7       6  0.857143\n",
       "16  1000001    5486       1 2020-01-09         7       6  0.857143\n",
       "17  1000002    7099       1 2020-01-09         4       4  1.000000\n",
       "18  1000002    9670       1 2020-01-09         4       4  1.000000\n",
       "19  1000001    7270       1 2020-01-01         0       0       NaN\n",
       "20  1000001     860       1 2020-01-01         0       0       NaN\n",
       "21  1000001    5390       0 2020-01-01         0       0       NaN\n",
       "22  1000001    5191       1 2020-01-01         0       0       NaN\n",
       "23  1000001    5734       0 2020-01-01         0       0       NaN\n",
       "24  1000001    6265       1 2020-01-01         0       0       NaN\n",
       "25  1000001     466       1 2020-01-01         0       0       NaN\n",
       "26  1000001    4426       1 2020-01-01         0       0       NaN\n",
       "27  1000001    5578       1 2020-01-01         0       0       NaN\n",
       "28  1000001    8322       0 2020-01-01         0       0       NaN\n",
       "29  1000002    1685       1 2020-01-01         0       0       NaN\n",
       "30  1000002     769       1 2020-01-01         0       0       NaN\n",
       "31  1000002    6949       1 2020-01-01         0       0       NaN\n",
       "32  1000002    2433       1 2020-01-01         0       0       NaN\n",
       "33  1000002    5311       1 2020-01-01         0       0       NaN\n",
       "34  1000001    5051       1 2020-01-02        10       7  0.700000\n",
       "35  1000001    6420       1 2020-01-02        10       7  0.700000\n",
       "36  1000001    1184       1 2020-01-02        10       7  0.700000\n",
       "37  1000001    4555       1 2020-01-02        10       7  0.700000\n",
       "38  1000001    3385       1 2020-01-02        10       7  0.700000\n",
       "39  1000002    6396       1 2020-01-02         5       5  1.000000\n",
       "40  1000002    8666       1 2020-01-02         5       5  1.000000\n",
       "41  1000002    9274       1 2020-01-02         5       5  1.000000\n",
       "42  1000002    2558       1 2020-01-02         5       5  1.000000\n",
       "43  1000002    7849       1 2020-01-02         5       5  1.000000\n",
       "44  1000001    2047       1 2020-01-03        15      12  0.800000\n",
       "45  1000002    2747       1 2020-01-03        10      10  1.000000\n",
       "46  1000001    9167       0 2020-01-04        16      13  0.812500\n",
       "47  1000001    9998       0 2020-01-04        16      13  0.812500\n",
       "48  1000002     189       1 2020-01-04        11      11  1.000000\n",
       "49  1000002    2734       1 2020-01-04        11      11  1.000000\n",
       "50  1000001    3005       1 2020-01-05         8       6  0.750000"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can apply the same technique for the last 7 days."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemid</th>\n",
       "      <th>userid</th>\n",
       "      <th>action</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>count_3D</th>\n",
       "      <th>sum_3D</th>\n",
       "      <th>avg_3D</th>\n",
       "      <th>count_7D</th>\n",
       "      <th>sum_7D</th>\n",
       "      <th>avg_7D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000001</td>\n",
       "      <td>4658</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>18</td>\n",
       "      <td>13</td>\n",
       "      <td>0.722222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000001</td>\n",
       "      <td>1899</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>18</td>\n",
       "      <td>13</td>\n",
       "      <td>0.722222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000002</td>\n",
       "      <td>7734</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000002</td>\n",
       "      <td>1267</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000001</td>\n",
       "      <td>1528</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>21</td>\n",
       "      <td>14</td>\n",
       "      <td>0.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1000002</td>\n",
       "      <td>3556</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1000001</td>\n",
       "      <td>3890</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>22</td>\n",
       "      <td>15</td>\n",
       "      <td>0.681818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8838</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>22</td>\n",
       "      <td>15</td>\n",
       "      <td>0.681818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5393</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>22</td>\n",
       "      <td>15</td>\n",
       "      <td>0.681818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8792</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>22</td>\n",
       "      <td>15</td>\n",
       "      <td>0.681818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8433</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>22</td>\n",
       "      <td>15</td>\n",
       "      <td>0.681818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1000001</td>\n",
       "      <td>7513</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>22</td>\n",
       "      <td>15</td>\n",
       "      <td>0.681818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2612</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1000002</td>\n",
       "      <td>7041</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1000002</td>\n",
       "      <td>9555</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1000001</td>\n",
       "      <td>6235</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>18</td>\n",
       "      <td>13</td>\n",
       "      <td>0.722222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5486</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>18</td>\n",
       "      <td>13</td>\n",
       "      <td>0.722222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1000002</td>\n",
       "      <td>7099</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1000002</td>\n",
       "      <td>9670</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1000001</td>\n",
       "      <td>7270</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1000001</td>\n",
       "      <td>860</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5390</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5191</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5734</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1000001</td>\n",
       "      <td>6265</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1000001</td>\n",
       "      <td>466</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1000001</td>\n",
       "      <td>4426</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5578</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1000001</td>\n",
       "      <td>8322</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1000002</td>\n",
       "      <td>1685</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1000002</td>\n",
       "      <td>769</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1000002</td>\n",
       "      <td>6949</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2433</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1000002</td>\n",
       "      <td>5311</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>1000001</td>\n",
       "      <td>5051</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1000001</td>\n",
       "      <td>6420</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1000001</td>\n",
       "      <td>1184</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>1000001</td>\n",
       "      <td>4555</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1000001</td>\n",
       "      <td>3385</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1000002</td>\n",
       "      <td>6396</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1000002</td>\n",
       "      <td>8666</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>1000002</td>\n",
       "      <td>9274</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2558</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>1000002</td>\n",
       "      <td>7849</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>1000001</td>\n",
       "      <td>2047</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>0.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2747</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>1000001</td>\n",
       "      <td>9167</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "      <td>0.812500</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "      <td>0.812500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1000001</td>\n",
       "      <td>9998</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "      <td>0.812500</td>\n",
       "      <td>16</td>\n",
       "      <td>13</td>\n",
       "      <td>0.812500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>1000002</td>\n",
       "      <td>189</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>1000002</td>\n",
       "      <td>2734</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>1000001</td>\n",
       "      <td>3005</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>18</td>\n",
       "      <td>13</td>\n",
       "      <td>0.722222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     itemid  userid  action  timestamp  count_3D  sum_3D    avg_3D  count_7D  \\\n",
       "0   1000001    4658       0 2020-01-05         8       6  0.750000        18   \n",
       "1   1000001    1899       0 2020-01-05         8       6  0.750000        18   \n",
       "2   1000002    7734       1 2020-01-05         8       8  1.000000        13   \n",
       "3   1000002    1267       1 2020-01-05         8       8  1.000000        13   \n",
       "4   1000001    1528       1 2020-01-07         6       2  0.333333        21   \n",
       "5   1000002    3556       1 2020-01-07         5       5  1.000000        15   \n",
       "6   1000001    3890       1 2020-01-08         4       2  0.500000        22   \n",
       "7   1000001    8838       1 2020-01-08         4       2  0.500000        22   \n",
       "8   1000001    5393       1 2020-01-08         4       2  0.500000        22   \n",
       "9   1000001    8792       1 2020-01-08         4       2  0.500000        22   \n",
       "10  1000001    8433       0 2020-01-08         4       2  0.500000        22   \n",
       "11  1000001    7513       1 2020-01-08         4       2  0.500000        22   \n",
       "12  1000002    2612       1 2020-01-08         3       3  1.000000        16   \n",
       "13  1000002    7041       1 2020-01-08         3       3  1.000000        16   \n",
       "14  1000002    9555       1 2020-01-08         3       3  1.000000        16   \n",
       "15  1000001    6235       1 2020-01-09         7       6  0.857143        18   \n",
       "16  1000001    5486       1 2020-01-09         7       6  0.857143        18   \n",
       "17  1000002    7099       1 2020-01-09         4       4  1.000000        14   \n",
       "18  1000002    9670       1 2020-01-09         4       4  1.000000        14   \n",
       "19  1000001    7270       1 2020-01-01         0       0       NaN         0   \n",
       "20  1000001     860       1 2020-01-01         0       0       NaN         0   \n",
       "21  1000001    5390       0 2020-01-01         0       0       NaN         0   \n",
       "22  1000001    5191       1 2020-01-01         0       0       NaN         0   \n",
       "23  1000001    5734       0 2020-01-01         0       0       NaN         0   \n",
       "24  1000001    6265       1 2020-01-01         0       0       NaN         0   \n",
       "25  1000001     466       1 2020-01-01         0       0       NaN         0   \n",
       "26  1000001    4426       1 2020-01-01         0       0       NaN         0   \n",
       "27  1000001    5578       1 2020-01-01         0       0       NaN         0   \n",
       "28  1000001    8322       0 2020-01-01         0       0       NaN         0   \n",
       "29  1000002    1685       1 2020-01-01         0       0       NaN         0   \n",
       "30  1000002     769       1 2020-01-01         0       0       NaN         0   \n",
       "31  1000002    6949       1 2020-01-01         0       0       NaN         0   \n",
       "32  1000002    2433       1 2020-01-01         0       0       NaN         0   \n",
       "33  1000002    5311       1 2020-01-01         0       0       NaN         0   \n",
       "34  1000001    5051       1 2020-01-02        10       7  0.700000        10   \n",
       "35  1000001    6420       1 2020-01-02        10       7  0.700000        10   \n",
       "36  1000001    1184       1 2020-01-02        10       7  0.700000        10   \n",
       "37  1000001    4555       1 2020-01-02        10       7  0.700000        10   \n",
       "38  1000001    3385       1 2020-01-02        10       7  0.700000        10   \n",
       "39  1000002    6396       1 2020-01-02         5       5  1.000000         5   \n",
       "40  1000002    8666       1 2020-01-02         5       5  1.000000         5   \n",
       "41  1000002    9274       1 2020-01-02         5       5  1.000000         5   \n",
       "42  1000002    2558       1 2020-01-02         5       5  1.000000         5   \n",
       "43  1000002    7849       1 2020-01-02         5       5  1.000000         5   \n",
       "44  1000001    2047       1 2020-01-03        15      12  0.800000        15   \n",
       "45  1000002    2747       1 2020-01-03        10      10  1.000000        10   \n",
       "46  1000001    9167       0 2020-01-04        16      13  0.812500        16   \n",
       "47  1000001    9998       0 2020-01-04        16      13  0.812500        16   \n",
       "48  1000002     189       1 2020-01-04        11      11  1.000000        11   \n",
       "49  1000002    2734       1 2020-01-04        11      11  1.000000        11   \n",
       "50  1000001    3005       1 2020-01-05         8       6  0.750000        18   \n",
       "\n",
       "    sum_7D    avg_7D  \n",
       "0       13  0.722222  \n",
       "1       13  0.722222  \n",
       "2       13  1.000000  \n",
       "3       13  1.000000  \n",
       "4       14  0.666667  \n",
       "5       15  1.000000  \n",
       "6       15  0.681818  \n",
       "7       15  0.681818  \n",
       "8       15  0.681818  \n",
       "9       15  0.681818  \n",
       "10      15  0.681818  \n",
       "11      15  0.681818  \n",
       "12      16  1.000000  \n",
       "13      16  1.000000  \n",
       "14      16  1.000000  \n",
       "15      13  0.722222  \n",
       "16      13  0.722222  \n",
       "17      14  1.000000  \n",
       "18      14  1.000000  \n",
       "19       0       NaN  \n",
       "20       0       NaN  \n",
       "21       0       NaN  \n",
       "22       0       NaN  \n",
       "23       0       NaN  \n",
       "24       0       NaN  \n",
       "25       0       NaN  \n",
       "26       0       NaN  \n",
       "27       0       NaN  \n",
       "28       0       NaN  \n",
       "29       0       NaN  \n",
       "30       0       NaN  \n",
       "31       0       NaN  \n",
       "32       0       NaN  \n",
       "33       0       NaN  \n",
       "34       7  0.700000  \n",
       "35       7  0.700000  \n",
       "36       7  0.700000  \n",
       "37       7  0.700000  \n",
       "38       7  0.700000  \n",
       "39       5  1.000000  \n",
       "40       5  1.000000  \n",
       "41       5  1.000000  \n",
       "42       5  1.000000  \n",
       "43       5  1.000000  \n",
       "44      12  0.800000  \n",
       "45      10  1.000000  \n",
       "46      13  0.812500  \n",
       "47      13  0.812500  \n",
       "48      11  1.000000  \n",
       "49      11  1.000000  \n",
       "50      13  0.722222  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "offset = '7D'\n",
    "\n",
    "data_window_roll = data_window[['itemid', 'count', 'sum']].groupby(['itemid']).rolling(offset).sum().drop('itemid', axis=1)\n",
    "data_window_roll = data_window_roll.reset_index()\n",
    "data_window_roll.columns = ['itemid', 'timestamp', 'count_' + offset, 'sum_' + offset]\n",
    "data_window_roll[['count_' + offset, 'sum_' + offset]] = data_window_roll[['count_' + offset, 'sum_' + offset]].shift(1)\n",
    "data_window_roll.loc[data_window_roll['itemid']!=data_window_roll['itemid'].shift(1), ['count_' + offset, 'sum_' + offset]] = 0\n",
    "data_window_roll['avg_' + offset] = data_window_roll['sum_' + offset]/data_window_roll['count_' + offset]\n",
    "data = data.merge(data_window_roll, how='left', on=['itemid', 'timestamp'])\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Practice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "### loading\n",
    "import pandas as pd\n",
    "import cudf\n",
    "import numpy as np\n",
    "import cupy\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "df_train = cudf.read_parquet('./data/train.parquet')\n",
    "df_valid = cudf.read_parquet('./data/valid.parquet')\n",
    "df_test = cudf.read_parquet('./data/test.parquet')\n",
    "\n",
    "df_train['brand'] = df_train['brand'].fillna('UNKNOWN')\n",
    "df_valid['brand'] = df_valid['brand'].fillna('UNKNOWN')\n",
    "df_test['brand'] = df_test['brand'].fillna('UNKNOWN')\n",
    "\n",
    "df_train['cat_0'] = df_train['cat_0'].fillna('UNKNOWN')\n",
    "df_valid['cat_0'] = df_valid['cat_0'].fillna('UNKNOWN')\n",
    "df_test['cat_0'] = df_test['cat_0'].fillna('UNKNOWN')\n",
    "\n",
    "df_train['cat_1'] = df_train['cat_1'].fillna('UNKNOWN')\n",
    "df_valid['cat_1'] = df_valid['cat_1'].fillna('UNKNOWN')\n",
    "df_test['cat_1'] = df_test['cat_1'].fillna('UNKNOWN')\n",
    "\n",
    "df_train['cat_2'] = df_train['cat_2'].fillna('UNKNOWN')\n",
    "df_valid['cat_2'] = df_valid['cat_2'].fillna('UNKNOWN')\n",
    "df_test['cat_2'] = df_test['cat_2'].fillna('UNKNOWN')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "cuDF does not support date32, right now. We use pandas to transform the timestamp in only date values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/conda/envs/nvtabular/lib/python3.7/site-packages/cudf/core/column/column.py:1396: UserWarning: Date32 values are not yet supported so this will be typecast to a Date64 value\n",
      "  UserWarning,\n"
     ]
    }
   ],
   "source": [
    "df_train['date'] = cudf.from_pandas(pd.to_datetime(df_train['timestamp'].to_pandas()).dt.date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's get the # of purchases per product in the 7 days before.\n",
    "\n",
    "**ToDo**:\n",
    "<li>Calculate the # of purchases of an item of the 7 previous days for each datapoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Optimisation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's compare a CPU with the GPU version."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rolling_window(df, col, offset):\n",
    "    data_window = df[[col, 'date', 'target']].groupby([col, 'date']).agg(['count', 'sum']).reset_index()\n",
    "    data_window.columns = [col, 'date', 'count', 'sum']\n",
    "    data_window.index = data_window['date']\n",
    "    \n",
    "    data_window_roll = data_window[[col, 'count', 'sum']].groupby([col]).rolling(offset).sum().drop(col, axis=1)\n",
    "    data_window_roll = data_window_roll.reset_index()\n",
    "    data_window_roll.columns = [col, 'date', 'count_' + offset, 'sum_' + offset]\n",
    "    data_window_roll[['count_' + offset, 'sum_' + offset]] = data_window_roll[['count_' + offset, 'sum_' + offset]].shift(1)\n",
    "    data_window_roll.loc[data_window_roll[col]!=data_window_roll[col].shift(1), ['count_' + offset, 'sum_' + offset]] = 0\n",
    "    data_window_roll['avg_' + offset] = data_window_roll['sum_' + offset]/data_window_roll['count_' + offset]\n",
    "    data = df.merge(data_window_roll, how='left', on=[col, 'date'])\n",
    "    return(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train_pd = df_train.to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 37.5 s, sys: 5.04 s, total: 42.5 s\n",
      "Wall time: 42.5 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "_ = rolling_window(df_train_pd, 'product_id', '5D')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 424 ms, sys: 232 ms, total: 656 ms\n",
      "Wall time: 655 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "_ = rolling_window(df_train, 'product_id', '5D')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In our experiments, we achieved a speedup of 372x"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We shutdown the kernel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'ok', 'restart': False}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "app = IPython.Application.instance()\n",
    "app.kernel.do_shutdown(False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
